package s;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.mysql.jdbc.Statement;


public class GetTags extends HttpServlet{

	private static final long serialVersionUID = 1L;
	private Properties props;
	private String propsPath;

	@Override
	public void init(ServletConfig config) throws ServletException {
		super.init(config);
		
		this.propsPath = this.getServletContext().getRealPath("/");
		this.props = new Properties();
		try {
			props.loadFromXML(new FileInputStream(propsPath + "properties.properties"));
		} catch (FileNotFoundException e) {e.printStackTrace();} 
		catch (IOException e) {e.printStackTrace();}
}
	
	
	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
		
			Connection connection = null;
			String response = "<tags>";
			
			try {
				Class.forName("com.mysql.jdbc.Driver");
				connection = DriverManager.getConnection(props.getProperty("DB_URL"), props.getProperty("DB_USER"), props.getProperty("DB_PASS"));
				Statement statement = (Statement) connection.createStatement();
				ResultSet rs = statement.executeQuery("SELECT tag.Tag as name, count(*) as n FROM tag, imgTag WHERE tag.IdTag = imgTag.IdTag GROUP BY tag.Tag ORDER BY n DESC;");
				 
				while(rs.next()){response = response.concat("<tag><name>"+rs.getString("name") + "</name><occurrences>"+rs.getInt("n")+"</occurrences></tag>");}
				
			} 
			catch (SQLException e1) {e1.printStackTrace();} catch (ClassNotFoundException e) {e.printStackTrace();}
			finally {
					try {connection.close();} 
					catch (SQLException e) {e.printStackTrace();}
			}
			
			response = response.concat("</tags>");
			PrintWriter pw = res.getWriter();
			pw.print(response);
	}
}